Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# REPLCOLS

The REPLCOLS function replaces some or all of the character columns in one multiline TEXT value with the columns of another. The function returns a multiline TEXT value composed of the resulting lines.

Columns refer to the character positions in each line of a multiline TEXT value. The first character in each line is in column one, the second is in column two, and so on.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

- When all arguments are TEXT values, the return value is TEXT.

- When all arguments are NTEXT values, the return value is NTEXT.

- When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

The number of lines in the return value is always the same as the number of lines in text-expression. When the columns text expression has fewer lines, REPLCOLS repeats its last line in each subsequent line of the return value.

Syntax

REPLCOLS(text-expression columns [start])

Arguments

text-expression

  The text expression in which you want to replace one or more columns.

columns

  A text expression containing one or more lines. This expression provides the columns to replace some or all of the columns in text-expression.

start

  An `INTEGER`, between 1 and 4,000, representing the column position at which to begin replacing. The column position of the first character in each line of text-expression is 1. When you do not specify start, replacement begins with Column 1. When you specify a starting column that is to the right of the last character in a given line in text-expression, the corresponding line in the return value will have spaces filling in the intervening columns. See Example 20-26, "Joining and Aligning Columns".

Examples

Example 20-26 Joining and Aligning Columns

In the following example, the `citylist` and `cityreps` lines are joined so that the values are aligned, one under the other. The replacement begins at Column 11. When JOINCOLS were used instead of REPLCOLS, the `cityreps` list would be misaligned.

The statement

    SHOW citylist

produces the following output.

    Boston
    Houston
    Chicago
    Denver

The statement

    SHOW cityreps

produces the following output.

    Brady
    Lopez
    Alfonso
    Cody

The statement

    SHOW REPLCOLS(citylist cityreps 11)

produces the following output.

    Boston    Brady
    Houston   Lopez
    Chicago   Alfonso
    Denver    Cody